package cn.com.adcc.utils;

import cn.com.adcc.bean.UploadFileLog;
import cn.com.adcc.bean.UploadFilePartLog;
import org.apache.log4j.Logger;
import org.joda.time.DateTime;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;

/**
 * Created by anzilu on 2015/10/10.
 */
public class SqliteAccess {

    private static Logger logger = Logger.getLogger(Configuration.class);
    private static final String path=System.getProperty("user.dir");
    private static final String dbFileName=Configuration.GetValueByKey(path+ File.separator+"config"+File.separator+"config.properties","DBFILENAME");
    private static ResultSet rs=null;
    private static Connection conn=null;

    public static boolean connect(){

        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:" + dbFileName);
            conn.setAutoCommit(true);
            return true;
        }
        catch (ClassNotFoundException e) {
            logger.error("Sqlite connecting error: "+e.getMessage());
            return false;
        }
        catch (SQLException e) {
            logger.error("Sqlite connecting error: " + e.getMessage());
            return false;
        }
    }

    public static boolean disconn(){

        if(conn!=null){

            try {
                conn.close();
                return true;
            }
            catch (SQLException e) {
                logger.error("Sqlite disconnecting error: " + e.getMessage());
                conn=null;
                return false;
            }
        }
        return true;
    }

    public static long insert(String tableName,UploadFilePartLog uplfplog){
        long id=-1;
        try {
            String sql="replace INTO "+tableName+" VALUES (null,?,?,?)";
            PreparedStatement psp=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            psp.setInt(1, uplfplog.getPartNumber());
            psp.setString(2,uplfplog.geteTag());
            psp.setInt(3,uplfplog.getFileLogId());
            psp.addBatch();
            psp.executeBatch();
            ResultSet rs=psp.getGeneratedKeys();
            if(rs.next()){
                id=rs.getLong(1);
            }
        }
        catch (SQLException e) {
            try {
                conn.rollback();
            }
            catch (SQLException e1) {
                logger.error("Sqlite rollback error: " + e.getMessage());
            }
            logger.error("Sqlite insert error: " + e.getMessage());
        }
        return id;
    }

    public static long insert(String tableName,UploadFileLog upflog){
        long id=-1;
        try {
            String sql="replace INTO "+tableName+" VALUES (null,?,?,?,?,?,?)";
            PreparedStatement psp=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

            psp.setString(1,upflog.getFilePath());
            psp.setString(2,upflog.getUploadId());
            psp.setInt(3, upflog.getStatus());
            psp.setString(4, upflog.getCreateTime().toString("yyyy-MM-dd HH:mm:ss"));
            psp.setString(5, upflog.getModifyTime().toString("yyyy-MM-dd HH:mm:ss"));
            psp.setInt(6,upflog.getIsPrior());
            psp.addBatch();
            psp.executeBatch();
            ResultSet rs=psp.getGeneratedKeys();
            if(rs.next()){
                id=rs.getLong(1);
            }

        }
        catch (SQLException e) {
            try {
                conn.rollback();
            }
            catch (SQLException e1) {
                logger.error("Sqlite rollback error: " + e.getMessage());
            }
            logger.error("Sqlite insert error: " + e.getMessage());

        }
        return id;
    }

    public static ResultSet query(String querySql){

        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(querySql);
            return rs;
        }
        catch (SQLException e) {
            logger.error("Sqlite executeQuery error: " + e.getMessage());
            return null;
        }

    }


    public static boolean clear(String tableName){

        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.execute("DELETE FROM "+tableName);
            return true;
        }
        catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                logger.error("Sqlite rollback error: " + e.getMessage());
            }
            logger.error("Sqlite executeQuery error: " + e.getMessage());
            return false;
        }
    }

    public static void main(String[] args){

        connect();

        UploadFileLog uploadFileLog=new UploadFileLog();
        uploadFileLog.setFilePath("D:\\ziptest\\addstreamtozip.zip");
        uploadFileLog.setCreateTime(new DateTime());
        uploadFileLog.setStatus(1);
        uploadFileLog.setModifyTime(new DateTime().plusHours(3));
        uploadFileLog.setIsPrior(0);
        long id=insert("uploadfilelog",uploadFileLog);
        System.out.println("uploadfilelog当前插入记录主键："+id);
        //clear("uploadfilelog");

        UploadFilePartLog uploadFilePartLog=new UploadFilePartLog();
        uploadFilePartLog.seteTag("123413523463457356737");
        uploadFilePartLog.setFileLogId(1);
        uploadFilePartLog.setPartNumber(1);
        long partid=insert("uploadfilepartlog",uploadFilePartLog);
        System.out.println("uploadfilepartlog当前插入记录主键："+partid);
        disconn();
    }
}
